# encoding: UTF-8
# user：admin at 2018/10/17

import xlrd
import pymysql
from excel2Sqlite import read2Sqlite
class OprationExcel():
    def __init__(self):
        self.MYSQL_HOST = '192.168.74.107'
        self.MYSQL_PORT = 3306
        self.MYSQL_USER = 'root'
        self.MYSQL_PWD = 'root'
        self.MYSQL_DB = 'home_db'

        self.db = pymysql.connect(host=self.MYSQL_HOST, port=self.MYSQL_PORT,
                                  user=self.MYSQL_USER, passwd=self.MYSQL_PWD,
                                  db=self.MYSQL_DB,charset='utf8')

    def readExcel(self,path,files):
        dicData = {}
        rs=read2Sqlite()
        for file in files:
            filepath=path+'/'+file
            dicData['name'] = file.split('-')[1].split('.')[0]

            data = xlrd.open_workbook(filepath)

            yanglao = data.sheet_by_name('养老保险')
            yiliao = data.sheet_by_name('医疗保险')
            gongshang = data.sheet_by_name('工伤保险')
            shiye = data.sheet_by_name('失业保险')
            shengyu = data.sheet_by_name('生育保险')

            for nrows_one in range(1, yanglao.nrows):

                if yanglao.cell_value(nrows_one, 2) == '113804':
                    dicData['fdate'] = yanglao.cell_value(nrows_one, 1)
                    dicData['fyanglaoje'] = float(yanglao.cell_value(nrows_one, 7))

                    if yiliao.nrows - 3 >= nrows_one:
                        if yiliao.cell_value(nrows_one, 2) == '113804':
                            dicData['fyiliaoje'] = float(yiliao.cell_value(nrows_one, 8))
                        else:
                            dicData['fyiliaoje'] = 0
                    else:
                        dicData['fyiliaoje'] = 0

                    if gongshang.nrows - 3 >= nrows_one:
                        if gongshang.cell_value(nrows_one, 2) == '113804':
                            dicData['fgongshangje'] = float(gongshang.cell_value(nrows_one, 7))
                        else:
                            dicData['fgongshangje'] = 0
                    else:
                        dicData['fgongshangje'] = 0

                    if shiye.nrows - 3 >= nrows_one:
                        if shiye.cell_value(nrows_one, 2) == '113804':
                            dicData['fshiyeje'] = float(shiye.cell_value(nrows_one, 7))
                        else:
                            dicData['fshiyeje'] = 0
                    else:
                        dicData['fshiyeje'] = 0

                    if shengyu.nrows - 3 >= nrows_one:
                        if shengyu.cell_value(nrows_one, 2) == '113804':
                            dicData['fshengyuje'] = float(shengyu.cell_value(nrows_one, 7))
                        else:
                            dicData['fshengyuje'] = 0
                    else:
                        dicData['fshengyuje'] = 0

                    #self._write2Mysql(dicData)
                    result,message=rs.writeDB(dicData)
                    if result==True:
                        pass
                    else:
                        print('【{}】的【{}】社保数据写入数据库错误,错误原因为：{}'.format(dicData['name'],dicData['fdate'],message))
                else:
                    continue

        return True

    def _write2Mysql(self,dictData):
        cur =self.db.cursor()

        selectSQL = "select exists(select 1 from t_ygcbxx where name=%(name)s and fdate=%(fdate)s)"

        value = {
            'name': dictData['name'],
            'fdate': dictData['fdate']
        }
        cur.execute(selectSQL, value)
        cnt = cur.fetchall()[0]

        if cnt[0] == 1:
            print('【' + dictData['name'] + '】的【' + dictData['fdate'] + '】社保数据，已经存在！')
        else:
            sql = "insert into t_ygcbxx(name,fdate,fyanglaoje,fyiliaoje,fgongshangje,fshiyeje,fshengyuje) " \
                  "values(%(name)s,%(fdate)s,%(fyanglaoje)s,%(fyiliaoje)s,%(fgongshangje)s," \
                  "%(fshiyeje)s,%(fshengyuje)s)"

            cur.execute(sql, dictData)
            self.db.commit()

